﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using Npgsql;

namespace NORM.DataBase
{
    #region PostgreSqlHelper

    /// <summary>
    /// Npgsql 数据库操作类
    /// </summary>
    internal class PostgreSqlHelper
    {
        public NpgsqlTransaction tran = null;

        public int Execute(string connectionString, List<string> cmdTextList)
        {
            return 0;
        }

        public int Execute(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Execute(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteNonQuery();
            }
        }

        public object GetScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public object GetScalar(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteScalar();
            }
        }

        public DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (NpgsqlDataAdapter da = new NpgsqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
        }

        public DataSet GetDataSet(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                DataSet ds = new DataSet();
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                try
                {
                    using (NpgsqlDataAdapter da = new NpgsqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        da.Fill(ds);
                        return ds;
                    }
                }
                catch (Exception ex)
                {
                    ds = new DataSet();
                    NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable("Table");
                    DataTable schemaTable = reader.GetSchemaTable();
                    foreach (DataRow dataRow in (InternalDataCollectionBase)schemaTable.Rows)
                    {
                        string typeName = string.Concat(dataRow["DataType"]);
                        string columnName = string.Concat(dataRow["ColumnName"]);
                        switch (typeName)
                        {
                            case "NpgsqlTypes.BitString":
                                typeName = "System.Boolean";
                                break;
                        }
                        table.Columns.Add(new DataColumn(columnName, Type.GetType(typeName)));
                    }

                    while (reader.Read())
                    {
                        DataRow row = table.NewRow();
                        foreach (DataColumn dc in table.Columns)//InternalDataCollectionBase
                        {
                            object Value = reader[dc.ColumnName];
                            if (Value != null && !DBNull.Value.Equals(Value))
                                row[dc.ColumnName] = Value;
                        }
                        table.Rows.Add(row);
                    }

                    reader.Dispose();
                    ds.Tables.Add(table);
                    ds.Tables.Add(schemaTable);

                    //未开启事务，则查询完成关闭连接
                    //if (!TransactionBegin)
                    //    conn.Close();

                }
                return ds;
            }
        }

        public DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return GetDataSet(connectionString, cmdType, cmdText, cmdParms).Tables[0];
        }

        public DataTable GetDataTable(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return GetDataSet(conn, cmdType, cmdText, cmdParms).Tables[0];
        }

        public DbDataReader GetReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }

        public DbDataReader GetReader(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        
        public DataTable GetDbSchema(string connectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT table_name,table_type from ( ");
            strSql.Append("SELECT tablename as table_name, 'u' as table_type   FROM   pg_tables  where schemaname='public' ");
            strSql.Append("UNION SELECT viewname as table_name, 'v' as table_type from pg_views where schemaname='public' ");
            strSql.Append("UNION SELECT proname as table_name , 'f' as table_type from pg_proc where provolatile='v'  and prolang='11859' ");
            strSql.Append(") sys_objects ");
            strSql.Append("ORDER BY table_type ASC,table_name ASC ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "u": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "p": ttype = "procedure"; break;
                    case "fn":
                    case "f": ttype = "function"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public DataTable GetDbSchema(string connectionString, string collectionName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT table_name,table_type from ( ");
            strSql.Append("SELECT tablename as table_name, 'u' as table_type   FROM   pg_tables  where schemaname='public' ");
            strSql.Append("UNION SELECT viewname as table_name, 'v' as table_type from pg_views where schemaname='public' ");
            strSql.Append("UNION SELECT proname as table_name , 'f' as table_type from pg_proc where provolatile='v'  and prolang='11859' ");
            strSql.Append(") sys_objects ");
            strSql.Append("WHERE table_type='" + collectionName .ToLower()+ "'");
            strSql.Append("ORDER BY table_type ASC,table_name ASC ");
            
            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "u": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "p": ttype = "procedure"; break;
                    case "fn":
                    case "f": ttype = "function"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public bool AddColumDescribtion(string connectionString, string tableName, string columnName, string Descrition)
        {
            return true;
        }

        public DataTable GetTables(string connectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select [type] as [table_type] ,[tbl_name] as [table_name] from Npgsql_master  ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "u": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "p": ttype = "procedure"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public DataTable GetTableColumns(string connectionString, string tableName)
        {
            DataTable dt = new DataTable(tableName);
            dt.Columns.Add(new DataColumn("colorder", typeof(Int32)));
            dt.Columns.Add(new DataColumn("ColumnName", typeof(string)));
            dt.Columns.Add(new DataColumn("TypeName", typeof(string)));
            dt.Columns.Add(new DataColumn("Length", typeof(string)));
            dt.Columns.Add(new DataColumn("CisNull", typeof(string)));
            dt.Columns.Add(new DataColumn("Describ", typeof(string)));

            string strSql = "SELECT a.attnum as \"colorder\",a.attname as \"name\", ";
            strSql += " format_type(a.atttypid,a.atttypmod) as \"type\",(atttypmod - 4 ) as \"Length\", ";
            strSql += " ( case a.attnotnull when true then '1' else '0' end )  as \"notnull\" , ";
            strSql += " ( SELECT contype from pg_constraint WHERE contype='p' and conrelid =c.oid and conkey[1]=a.attnum LIMIT 1 ) as \"pk\", ";
            strSql += " col_description(a.attrelid,a.attnum) as \"remark\" ";
            strSql += " FROM pg_class as c ";
            strSql += " LEFT JOIN pg_attribute as a on a.attrelid = c.oid ";
            strSql += " LEFT JOIN pg_type as t on a.attrelid=t.oid  ";
            strSql += " where c.relname = '" + tableName + "' and a.attnum>0 ;  ";
            DataTable dt_columns = GetDataTable(connectionString, CommandType.Text, strSql, null);
            int index = 1;
            foreach (DataRow dr in dt_columns.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["colorder"] = index;
                newrow["ColumnName"] = dr["name"];
                string len = dr["type"] + "";
                string typename = dr["type"] + "";
                if (typename.Contains("(") && typename.Contains(")"))
                {
                    len = len.Substring(len.LastIndexOf("(") + 1);
                    len = len.Substring(0, len.LastIndexOf(")"));
                    typename = typename.Substring(0, typename.LastIndexOf("("));
                }
                else
                {
                    len = "";
                }
                newrow["TypeName"] = typename;//nvarchar(50)
                newrow["Length"] = len;

                string pk = "";
                pk = dr["pk"] + "";
                if (pk == "p")
                {
                    pk = "pk";
                }
                string isnull = dr["notnull"] + "";
                if (isnull == "1")
                {
                    isnull = "not null";
                }
                else
                {
                    isnull = "null";
                }

                newrow["CisNull"] = pk + "," + isnull + ",";
                newrow["Describ"] = dr["remark"];
                dt.Rows.Add(newrow);
                index++;
            }

            return dt;
        }

        public bool RenameTable(string connectionString, string OldName, string NewName)
        {
            return false;
        }
        
        public bool UpdateDataSet(string connectionString, DataSet dataSet, string srcTable)
        {
            bool rvl = false;
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(srcTable, conn))
                {
                    NpgsqlCommandBuilder scb = new NpgsqlCommandBuilder(da);
                    da.InsertCommand = scb.GetInsertCommand();
                    da.UpdateCommand = scb.GetUpdateCommand();
                    da.DeleteCommand = scb.GetDeleteCommand();
                    da.Update(dataSet);
                    rvl = true;
                }
            }
            return rvl;
        }

        public bool UpdateDataSet(DbConnection conn, DataSet dataSet, string srcTable)
        {
            bool rvl = false;
            using (NpgsqlCommand cmd = new NpgsqlCommand(srcTable, (NpgsqlConnection)conn))
            {
                cmd.Transaction = tran;
                using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
                {
                    NpgsqlCommandBuilder scb = new NpgsqlCommandBuilder(da);
                    da.InsertCommand = scb.GetInsertCommand();
                    da.UpdateCommand = scb.GetUpdateCommand();
                    da.DeleteCommand = scb.GetDeleteCommand();
                    da.Update(dataSet);
                    rvl = true;
                }
            }
            return rvl;
        } 

        public DataSet GetListByPage(string connectionString, string strTab, string strFields, string strWhere, string orderby, int startIndex, int endIndex)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append(" select " + strFields + " from " + strTab + " T ");
                    if (strWhere.Trim() != "")
                    {
                        strSql.Append(" where " + strWhere + " ");
                    }
                    if (orderby.Trim() != "")
                    {
                        strSql.Append(" order by " + orderby + " ");
                    }
                    strSql.Append(" limit " + (endIndex + 1 - startIndex) + " offset " + (startIndex - 1) + " ");

                    PrepareCommand(cmd, conn, null, CommandType.Text, strSql.ToString(), null);
                    using (NpgsqlDataAdapter da = new NpgsqlDataAdapter())
                    {
                        DataSet ds = new DataSet();
                        da.SelectCommand = cmd;
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 生成要执行的命令
        /// </summary>
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {

            try
            {
                // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                if (cmdParms != null && cmdParms.Length > 0)
                    cmdText = cmdText.Replace("?", "@").Replace(":", "@");

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                if (trans != null)
                    cmd.Transaction = trans;
                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (DbParameter parm in cmdParms)
                    {
                        // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                        //parm.ParameterName = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                        //if (parm.Value == null)
                        //    parm.Value = DBNull.Value;
                        //cmd.Parameters.Add(parm);

                        // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                        string name = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                        object value = parm.Value == null ? DBNull.Value : parm.Value;
                        DbParameter p = new NpgsqlParameter(name, value);
                        cmd.Parameters.Add(p);

                    }
                }
            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.DataBaseExceptoin, ex.Message);
            }
        }
    }

    #endregion
}
